package dao.impl;

import dao.ComcollectDao;
import pojo.Comcollect;
import pojo.User;
import util.BaseDao;
import util.PageBean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 商品收藏表的dao层接口实现类
 * @author 代传斌
 */
public class ComcollectDaoImpl implements ComcollectDao {


    /**
     * 增加收藏
     */
    @Override
    public int addComcollect(Comcollect cl) {
        BaseDao bd=new BaseDao();
        //编写sql语句
        String sql="insert into comcollect values(sequence_ct_id.nextval,?,?,?)";
        //调用通用的增删改的方法
        int num=bd.cud(sql,cl.getUId(),cl.getWareId(),cl.getuNickName());
        return num;
    }

    /**
     * 删除收藏
     */
    @Override
    public int deleteComcollect(int ctId) {
        BaseDao bd=new BaseDao();
        //编写sql语句
        String sql="delete from comcollect where ct_id=?";
        //调用通用的增删改的方法
        int num=bd.cud(sql,ctId);
        return num;
    }

    /**
     * 修改收藏
     */
    @Override
    public int updateComcollect(Comcollect cl) {
        BaseDao bd=new BaseDao();
        //编写sql语句
        String sql="update comcollect set ct_id=?,u_id=?,ware_id=?,u_nickname=?, where ct_id=?";
        //调用通用的增删改的方法
        int num=bd.cud(sql,cl.getCtId(),cl.getUId(),cl.getWareId(),cl.getuNickName());
        return num;
    }

    /**
     * 根据收藏id查找收藏
     */
    @Override
    public Comcollect findComcollect(int ctId) {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        Comcollect cl=new Comcollect();
        BaseDao bd=new BaseDao();
        try {
            conn=bd.getConnection();
            //编写sql语句
            String sql="select * from comcollect where ct_id=?";
            //执行sql语句
            ps=conn.prepareStatement(sql);
            ps.setInt(1,ctId);
            rs=ps.executeQuery();
            //循环赋值
            while (rs.next()){
                cl.setCtId(rs.getInt("ct_id"));
                cl.setUId(rs.getInt("u_id"));
                cl.setWareId(rs.getInt("ware_id"));
                cl.setuNickName(rs.getString("u_nickname"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //释放连接池
            bd.release(rs, ps, conn);
        }
        return cl;
    }



    /**
     * 查询所有收藏信息
     */
    @Override
    public List<Comcollect> listComcollect() {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        List<Comcollect> lc=new ArrayList<>();
        BaseDao bd=new BaseDao();
        try {
            conn=bd.getConnection();
            //编写sql语句
            String sql="select*from comcollect";
            //执行sql语句
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            //循环赋值
            while (rs.next()){
                Comcollect cl=new Comcollect();
                cl.setCtId(rs.getInt("ct_id"));
                cl.setUId(rs.getInt("u_id"));
                cl.setWareId(rs.getInt("ware_id"));
                cl.setuNickName(rs.getString("u_nickname"));
                lc.add(cl);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //释放连接池
            bd.release(rs, ps, conn);
        }
        return lc;
    }

    /**
     * 分页查询
     * @param pb
     * @return
     */
    @Override
    public PageBean ComcollectList(PageBean pb, int ctId, String uNickName) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        BaseDao bd=new BaseDao();
        List<Comcollect> lc=new ArrayList<Comcollect>();
        try {
            con = bd.getConnection();
            //判断相关条件是否有值
            String s="";
            if(0!=ctId){
                s+=" and ctId="+ctId;
            }
            if(null!=uNickName){
                s+=" and u_nickName like '%"+uNickName+"%'";
            }
            //编写sql语句(sql)
            /*String sql=" SELECT * FROM ( " +
                    " SELECT ROWNUM r,dc.*，c.name FROM d_cart dc" +
                    " INNER JOIN Customers c ON c.id=dc.cuid " +
                    " WHERE 1=1 "+s+" and ROWNUM<=?" +
                    " )WHERE r>?";*/

            String sql=" SELECT * FROM ( " +
                    "  SELECT ROWNUM r,comcollect.u_id,ct_id,ware_id,e_user.u_nickname FROM comcollect " +
                    "  INNER JOIN e_user ON comcollect.u_id=e_user.u_id " +
                    "  WHERE 1=1 "+s+" and ROWNUM<=? " +
                    " )WHERE r>?";
            //执行sql语句
            ps = con.prepareStatement(sql);
            //给占位符赋值
            ps.setInt(1,pb.getPageIndex()*pb.getPageSize());
            ps.setInt(2,pb.getPageSize()*(pb.getPageIndex()-1));
            rs = ps.executeQuery();
            //循环赋值
            while (rs.next()){
                Comcollect cl=new Comcollect();
                cl.setCtId(rs.getInt("ct_id"));
                cl.setUId(rs.getInt("u_id"));
                cl.setWareId(rs.getInt("ware_id"));
                cl.setuNickName(rs.getString("u_nickname"));
                lc.add(cl);
            }
            pb.setList(lc);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //释放连接池
            bd.release(rs, ps, con);
        }
        return pb;
    }

    /**
     * 查询记录总数
     * @return
     */
    @Override
    public int countComcollect(int ctId, String uNickName) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        BaseDao bd=new BaseDao();
        int count=0;
        try {
            //判断相关条件是否有值
            String s="";
            if(0!=ctId){
                s+=" and ctId="+ctId;
            }
            if(null!=uNickName){
                s+=" and e_nickname like '%"+uNickName+"%'";
            }
            con = bd.getConnection();
            //编写sql语句
            String sql="select count(*) from comcollect dc INNER JOIN e_user e ON e.u_id=e.u_id where 1=1"+s;
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()) {
                count=rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //释放连接池
            bd.release(rs, ps, con);
        }
        return count;
    }

    /**
     * 分页查询会员信息
     * @param pb
     * @param name
     * @return
     */
    @Override
    public PageBean pageList(PageBean pb, String name) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        BaseDao bd=new BaseDao();
        List<User> lc=new ArrayList<User>();
        try {
            con = bd.getConnection();
            //判断相关条件是否有值
            String s="";

            if(null!=name){
                s+=" and u_nickName like '%"+name+"%'";
            }
            //编写sql语句(sql)
            /*String sql=" SELECT * FROM ( " +
                    " SELECT ROWNUM r,dc.*，c.name FROM d_cart dc" +
                    " INNER JOIN Customers c ON c.id=dc.cuid " +
                    " WHERE 1=1 "+s+" and ROWNUM<=?" +
                    " )WHERE r>?";*/

            String sql=" SELECT * FROM ( " +
                    "                      SELECT ROWNUM r,e_user.* FROM e_user " +
                    "                      INNER JOIN comcollect ON e_user.u_id=comcollect.u_id " +
                    "                      WHERE 1=1 and ROWNUM<=?" +
                    "                     )WHERE r>?";
            //执行sql语句
            ps = con.prepareStatement(sql);
            //给占位符赋值
            ps.setInt(1,pb.getPageIndex()*pb.getPageSize());
            ps.setInt(2,pb.getPageSize()*(pb.getPageIndex()-1));
            rs = ps.executeQuery();
            //循环赋值
            while (rs.next()){
                User u=new User();
                u.setuNickName(rs.getString("u_nickname"));
                lc.add(u);
            }
            pb.setList(lc);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //释放连接池
            bd.release(rs, ps, con);
        }
        return pb;
    }

    /**
     * 查询单个会员信息
     * @param uNickName
     * @return
     */
    @Override
    public User findByName(String uNickName) {

        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        User u=new User();
        BaseDao bd=new BaseDao();
        try {
            conn=bd.getConnection();
            //编写sql语句
            String sql="select * from e_user where e_unickname="+uNickName;
            //执行sql语句
            ps=conn.prepareStatement(sql);
            ps.setString(1,uNickName);
            rs=ps.executeQuery();
            //循环赋值
            while (rs.next()){
                u.setuIcon(rs.getString("u_icon"));
                u.setuID(rs.getInt("u_id"));
                u.setuAccount(rs.getString("u_account"));
                u.setuPassword(rs.getString("u_password"));
                u.setuNickName(rs.getString("u_nickname"));
                u.setuGender(rs.getInt("u_gender"));
                u.setuPhoneNumber(rs.getString("u_phonenumber"));
                u.setuEmail(rs.getString("u_email"));
                u.setuAddress(rs.getString("u_address"));
                u.setuIntegral(rs.getInt("u_integral"));
                u.setuRegdate(rs.getTimestamp("u_regdate"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //释放连接池
            bd.release(rs, ps, conn);
        }
        return u;
    }

    /**
     * 查询所有会员信息
     * @return
     */
    @Override
    public List<User> list() {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        List<User> lc=new ArrayList<>();
        BaseDao bd=new BaseDao();
        try {
            conn=bd.getConnection();
            //编写sql语句
            String sql="select*from e_user";
            //执行sql语句
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            //循环赋值
            while (rs.next()){
               User u=new User();
                u.setuIcon(rs.getString("u_icon"));
                u.setuID(rs.getInt("u_id"));
                u.setuAccount(rs.getString("u_account"));
                u.setuPassword(rs.getString("u_password"));
                u.setuNickName(rs.getString("u_nickname"));
                u.setuGender(rs.getInt("u_gender"));
                u.setuPhoneNumber(rs.getString("u_phonenumber"));
                u.setuEmail(rs.getString("u_email"));
                u.setuAddress(rs.getString("u_address"));
                u.setuIntegral(rs.getInt("u_integral"));
                u.setuRegdate(rs.getTimestamp("u_regdate"));
                lc.add(u);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //释放连接池
            bd.release(rs, ps, conn);
        }
        return lc;
    }


}